package com.example.demo.sqlutil;

import com.alibaba.druid.DbType;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr;
import com.alibaba.druid.sql.ast.expr.SQLCharExpr;
import com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr;
import com.alibaba.druid.sql.ast.expr.SQLIntegerExpr;
import com.alibaba.druid.sql.ast.statement.*;
import com.alibaba.druid.sql.dialect.sqlserver.ast.stmt.SQLServerInsertStatement;
import com.alibaba.druid.sql.dialect.sqlserver.ast.stmt.SQLServerUpdateStatement;
import com.alibaba.druid.util.JdbcConstants;
import com.alibaba.fastjson.JSON;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author li.dongquan
 * @Description: SQLServer解析
 * @Date 2020/10/16
 */
public class SqlServerParser {

    /**
     * 解析sql语句
     *
     * @param statement
     * @return
     */
    public void parse(String statement) {
        // 使用druid解析语句
        // 第一个参数为SQL语句
        // 第二个参数为解析的数据库类型
        List<com.alibaba.druid.sql.ast.SQLStatement> statementList = SQLUtils.parseStatements(statement, DbType.gaussdb);
        // 单语句解析，只有一条数据
        if (!statement.isEmpty()) {
            SQLStatement sqlStatement = statementList.get(0);
            if (sqlStatement instanceof SQLCreateTableStatement) {
                SQLCreateTableStatement createTableStatement = (SQLCreateTableStatement) sqlStatement;
                System.out.println("表名:" + createTableStatement.getTableName());
                createTableStatement.getTableElementList().forEach(sqlTableElement -> {
                    if (sqlTableElement instanceof SQLColumnDefinition) {
                        SQLColumnDefinition sqlColumnDefinition = (SQLColumnDefinition) sqlTableElement;
                        System.out.println("列信息：" + sqlColumnDefinition.getColumnName() + "-->" + sqlColumnDefinition.getDataType() + "-->" + sqlColumnDefinition.getComment() + "\t" + sqlColumnDefinition.toString());
                    }
                });
            } else if (sqlStatement instanceof SQLCreateViewStatement) {
                SQLCreateViewStatement sqlCreateViewStatement = (SQLCreateViewStatement) sqlStatement;
                System.out.println("视图名:" + sqlCreateViewStatement.getTableSource());
                sqlCreateViewStatement.getColumns().forEach(sqlTableElement -> {
                    if (sqlTableElement instanceof SQLColumnDefinition) {
                        SQLColumnDefinition sqlColumnDefinition = (SQLColumnDefinition) sqlTableElement;
                        System.out.println("列信息：" + sqlColumnDefinition.getColumnName() + "-->" + sqlColumnDefinition.getDataType() + "-->" + sqlColumnDefinition.getComment() + "\t" + sqlColumnDefinition.toString());
                    }
                });
            } else {
                System.out.println("不是建表/视图语句！");
            }
        }
    }

    private static Object getValue(SQLExpr value) {
        // TODO 判断更多的种类
        if (value instanceof SQLIntegerExpr) {
            // 值是数字
            return ((SQLIntegerExpr) value).getNumber();
        } else if (value instanceof SQLCharExpr) {
            // 值是字符串
            return ((SQLCharExpr) value).getText();
        }
        return null;
    }

    public static void main(String[] args) throws Exception {
        SqlServerParser sqlServerParser = new SqlServerParser();
        sqlServerParser.parse("CREATE OR REPLACE VIEW artc_view.artc_p_ch_cust_pos_rng_ern_sr(dw_stat_dt,dw_job_seq,dw_upd_tm,dw_etl_dt,dw_upd_dt,cust_uid,bbk_org_id,cur_mon_cst,cur_mon_ern,cur_mon_yld,prev_year_rng_yld,last_year_rng_yld,cur_year_rng_yld,late_3_year_rng_yld,late_1_year_rng_yld,late_3_mon_rng_yld,cur_mon_fnd_cst,cur_mon_fnd_ern,cur_mon_fnd_yld,prev_year_fnd_rng_yld,last_year_fnd_rng_yld,cur_year_fnd_rng_yld,late_3_year_fnd_rng_yld,late_1_year_fnd_rng_yld,late_3_mon_fnd_rng_yld) AS SELECT artc_p_ch_cust_pos_rng_ern_sr.dw_stat_dt, artc_p_ch_cust_pos_rng_ern_sr.dw_job_seq, artc_p_ch_cust_pos_rng_ern_sr.dw_upd_tm, artc_p_ch_cust_pos_rng_ern_sr.dw_etl_dt, artc_p_ch_cust_pos_rng_ern_sr.dw_upd_dt, artc_p_ch_cust_pos_rng_ern_sr.cust_uid, artc_p_ch_cust_pos_rng_ern_sr.bbk_org_id, artc_p_ch_cust_pos_rng_ern_sr.cur_mon_cst, artc_p_ch_cust_pos_rng_ern_sr.cur_mon_ern, artc_p_ch_cust_pos_rng_ern_sr.cur_mon_yld, artc_p_ch_cust_pos_rng_ern_sr.prev_year_rng_yld, artc_p_ch_cust_pos_rng_ern_sr.last_year_rng_yld, artc_p_ch_cust_pos_rng_ern_sr.cur_year_rng_yld, artc_p_ch_cust_pos_rng_ern_sr.late_3_year_rng_yld, artc_p_ch_cust_pos_rng_ern_sr.late_1_year_rng_yld, artc_p_ch_cust_pos_rng_ern_sr.late_3_mon_rng_yld, artc_p_ch_cust_pos_rng_ern_sr.cur_mon_fnd_cst, artc_p_ch_cust_pos_rng_ern_sr.cur_mon_fnd_ern, artc_p_ch_cust_pos_rng_ern_sr.cur_mon_fnd_yld, artc_p_ch_cust_pos_rng_ern_sr.prev_year_fnd_rng_yld, artc_p_ch_cust_pos_rng_ern_sr.last_year_fnd_rng_yld, artc_p_ch_cust_pos_rng_ern_sr.cur_year_fnd_rng_yld, artc_p_ch_cust_pos_rng_ern_sr.late_3_year_fnd_rng_yld, artc_p_ch_cust_pos_rng_ern_sr.late_1_year_fnd_rng_yld, artc_p_ch_cust_pos_rng_ern_sr.late_3_mon_fnd_rng_yld FROM artc_data.artc_p_ch_cust_pos_rng_ern_sr;");
        System.out.println(">>>>>>>>");
        sqlServerParser.parse("CREATE  TABLE artc_p_ch_cust_prod_mon_rng_ern_sr (\n" +
                "\tdw_stat_dt date NOT NULL,\n" +
                "\tdw_job_seq smallint NOT NULL,\n" +
                "\tdw_upd_tm time(0) without time zone NOT NULL,\n" +
                "\tdw_etl_dt date NOT NULL,\n" +
                "\tdw_upd_dt date NOT NULL,\n" +
                "\tcust_uid character varying(50) NOT NULL,\n" +
                "\tbbk_org_id character varying(20) NOT NULL,\n" +
                "\trtl_wlth_prod_id character varying(50) NOT NULL,\n" +
                "\tcst_01 numeric(18,2) NOT NULL,\n" +
                "\tcst_02 numeric(18,2) NOT NULL,\n" +
                "\tcst_03 numeric(18,2) NOT NULL,\n" +
                "\tcst_04 numeric(18,2) NOT NULL,\n" +
                "\tcst_05 numeric(18,2) NOT NULL,\n" +
                "\tcst_06 numeric(18,2) NOT NULL,\n" +
                "\tcst_07 numeric(18,2) NOT NULL,\n" +
                "\tcst_08 numeric(18,2) NOT NULL,\n" +
                "\tcst_09 numeric(18,2) NOT NULL,\n" +
                "\tcst_10 numeric(18,2) NOT NULL,\n" +
                "\tcst_11 numeric(18,2) NOT NULL,\n" +
                "\tcst_12 numeric(18,2) NOT NULL,\n" +
                "\tcst_13 numeric(18,2) NOT NULL,\n" +
                "\tcst_14 numeric(18,2) NOT NULL,\n" +
                "\tcst_15 numeric(18,2) NOT NULL,\n" +
                "\tcst_16 numeric(18,2) NOT NULL,\n" +
                "\tcst_17 numeric(18,2) NOT NULL,\n" +
                "\tcst_18 numeric(18,2) NOT NULL,\n" +
                "\tcst_19 numeric(18,2) NOT NULL,\n" +
                "\tcst_20 numeric(18,2) NOT NULL,\n" +
                "\tcst_21 numeric(18,2) NOT NULL,\n" +
                "\tcst_22 numeric(18,2) NOT NULL,\n" +
                "\tcst_23 numeric(18,2) NOT NULL,\n" +
                "\tcst_24 numeric(18,2) NOT NULL,\n" +
                "\tcst_25 numeric(18,2) NOT NULL,\n" +
                "\tcst_26 numeric(18,2) NOT NULL,\n" +
                "\tcst_27 numeric(18,2) NOT NULL,\n" +
                "\tcst_28 numeric(18,2) NOT NULL,\n" +
                "\tcst_29 numeric(18,2) NOT NULL,\n" +
                "\tcst_30 numeric(18,2) NOT NULL,\n" +
                "\tcst_31 numeric(18,2) NOT NULL,\n" +
                "\tcst_32 numeric(18,2) NOT NULL,\n" +
                "\tcst_33 numeric(18,2) NOT NULL,\n" +
                "\tcst_34 numeric(18,2) NOT NULL,\n" +
                "\tcst_35 numeric(18,2) NOT NULL,\n" +
                "\tcst_36 numeric(18,2) NOT NULL,\n" +
                "\tern_01 numeric(18,2) NOT NULL,\n" +
                "\tern_02 numeric(18,2) NOT NULL,\n" +
                "\tern_03 numeric(18,2) NOT NULL,\n" +
                "\tern_04 numeric(18,2) NOT NULL,\n" +
                "\tern_05 numeric(18,2) NOT NULL,\n" +
                "\tern_06 numeric(18,2) NOT NULL,\n" +
                "\tern_07 numeric(18,2) NOT NULL,\n" +
                "\tern_08 numeric(18,2) NOT NULL,\n" +
                "\tern_09 numeric(18,2) NOT NULL,\n" +
                "\tern_10 numeric(18,2) NOT NULL,\n" +
                "\tern_11 numeric(18,2) NOT NULL,\n" +
                "\tern_12 numeric(18,2) NOT NULL,\n" +
                "\tern_13 numeric(18,2) NOT NULL,\n" +
                "\tern_14 numeric(18,2) NOT NULL,\n" +
                "\tern_15 numeric(18,2) NOT NULL,\n" +
                "\tern_16 numeric(18,2) NOT NULL,\n" +
                "\tern_17 numeric(18,2) NOT NULL,\n" +
                "\tern_18 numeric(18,2) NOT NULL,\n" +
                "\tern_19 numeric(18,2) NOT NULL,\n" +
                "\tern_20 numeric(18,2) NOT NULL,\n" +
                "\tern_21 numeric(18,2) NOT NULL,\n" +
                "\tern_22 numeric(18,2) NOT NULL,\n" +
                "\tern_23 numeric(18,2) NOT NULL,\n" +
                "\tern_24 numeric(18,2) NOT NULL,\n" +
                "\tern_25 numeric(18,2) NOT NULL,\n" +
                "\tern_26 numeric(18,2) NOT NULL,\n" +
                "\tern_27 numeric(18,2) NOT NULL,\n" +
                "\tern_28 numeric(18,2) NOT NULL,\n" +
                "\tern_29 numeric(18,2) NOT NULL,\n" +
                "\tern_30 numeric(18,2) NOT NULL,\n" +
                "\tern_31 numeric(18,2) NOT NULL,\n" +
                "\tern_32 numeric(18,2) NOT NULL,\n" +
                "\tern_33 numeric(18,2) NOT NULL,\n" +
                "\tern_34 numeric(18,2) NOT NULL,\n" +
                "\tern_35 numeric(18,2) NOT NULL,\n" +
                "\tern_36 numeric(18,2) NOT NULL\n" +
                ");\n" +
                "COMMENT ON TABLE artc_p_ch_cust_prod_mon_rng_ern_sr IS 'P_CH_客户产品月度区间收益统计';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.dw_stat_dt IS '统计日期';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.dw_job_seq IS '作业序号';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.dw_upd_tm IS '更新时间';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.dw_etl_dt IS '翻牌日期';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.dw_upd_dt IS '更新日期';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cust_uid IS '客户UID';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.bbk_org_id IS '分行机构编号';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.rtl_wlth_prod_id IS '零售财富产品编号';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_01 IS '成本01';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_02 IS '成本02';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_03 IS '成本03';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_04 IS '成本04';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_05 IS '成本05';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_06 IS '成本06';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_07 IS '成本07';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_08 IS '成本08';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_09 IS '成本09';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_10 IS '成本10';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_11 IS '成本11';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_12 IS '成本12';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_13 IS '成本13';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_14 IS '成本14';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_15 IS '成本15';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_16 IS '成本16';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_17 IS '成本17';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_18 IS '成本18';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_19 IS '成本19';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_20 IS '成本20';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_21 IS '成本21';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_22 IS '成本22';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_23 IS '成本23';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_24 IS '成本24';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_25 IS '成本25';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_26 IS '成本26';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_27 IS '成本27';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_28 IS '成本28';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_29 IS '成本29';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_30 IS '成本30';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_31 IS '成本31';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_32 IS '成本32';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_33 IS '成本33';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_34 IS '成本34';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_35 IS '成本35';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.cst_36 IS '成本36';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_01 IS '收益01';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_02 IS '收益02';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_03 IS '收益03';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_04 IS '收益04';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_05 IS '收益05';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_06 IS '收益06';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_07 IS '收益07';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_08 IS '收益08';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_09 IS '收益09';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_10 IS '收益10';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_11 IS '收益11';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_12 IS '收益12';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_13 IS '收益13';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_14 IS '收益14';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_15 IS '收益15';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_16 IS '收益16';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_17 IS '收益17';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_18 IS '收益18';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_19 IS '收益19';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_20 IS '收益20';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_21 IS '收益21';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_22 IS '收益22';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_23 IS '收益23';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_24 IS '收益24';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_25 IS '收益25';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_26 IS '收益26';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_27 IS '收益27';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_28 IS '收益28';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_29 IS '收益29';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_30 IS '收益30';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_31 IS '收益31';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_32 IS '收益32';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_33 IS '收益33';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_34 IS '收益34';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_35 IS '收益35';\n" +
                "COMMENT ON COLUMN artc_p_ch_cust_prod_mon_rng_ern_sr.ern_36 IS '收益36';");
    }
}


